VWG Integration (Deep Dive)
This document provides a detailed overview of the end-to-end VWG integration flow, including:
- Importing VWG catalogue data (part prices and fluid details) into Cloud SQL
- Importing VWG blacklisted VIN data from the VWG API into Cloud SQL
- Enquiry-time VWG processing and enrichment
- VWG API integration flow
- UI behaviour for enriched parts and fluid lines
Overview
The VWG integration consists of two major execution flows:
-
Batch Import Flow
- Imports and maintains VWG reference data in Cloud SQL.
-
Enquiry-Time Processing Flow
- Validates the enquiry
- Calls VWG APIs
- Enriches response data using Cloud SQL lookup tables
- Returns enriched line items to the UI
Architecture
Core Components
Import Manager Service
Responsible for importing VWG reference data into Cloud SQL.
Inputs
- GCS files
- Parts prices
- Fluid details
- VWG API
- Blacklisted VIN list
Outputs
vwg_parts_pricevwg_fluid_detailsvwg_blacklisted_vin
Cloud SQL (PostgreSQL)
Stores all VWG reference and enrichment data.
Tables
vwg_parts_pricevwg_fluid_detailsvwg_blacklisted_vin
Used By
- Enquiry-time VWG processing service
Enquiry-Time Service
Handles VWG processing during enquiry execution.
Responsibilities
- Validate VWG eligibility
- Check VIN blacklist
- Call VWG packages-by-VIN API
- Enrich returned data using Cloud SQL
UI
Displays enriched VWG package details.
Capabilities
- Display enriched parts and fluid lines
- Allow editing of fluid pricing
- Display a warning indicator when a part price is missing within a package
- Show the following tooltip for incomplete package data:
This cannot be added because there is missing information in this package.
High-Level Runtime Flow
Import Manager
↓
Cloud SQL
↓
Enquiry Creation Process
↓
VWG API
↓
UI
Happy Path Flow
-
Import Manager imports:
- Parts prices
- Fluid details
- Blacklisted VINs
-
UI sends enquiry request with VIN.
-
Enquiry-time service:
- Validates eligibility
- Checks VIN blacklist
- Resolves lookup data from Cloud SQL
-
Service calls VWG packages-by-VIN API.
-
Service enriches package data.
-
UI receives enriched package lines.
Import Processing
Imports populate Cloud SQL lookup tables used during enquiry-time enrichment.
Import Types
Parts Price Import
Imports VWG part pricing data into Cloud SQL.
Fluid Details Import
Imports fluid descriptions and metadata into Cloud SQL.
VIN Blacklist Import
Fetches blacklisted VINs from VWG APIs and stores them in Cloud SQL.
Cloud SQL Tables
vwg_parts_price
Purpose
Stores VWG part price mappings.
Structure
| Column | Description |
|---|---|
record_identifier | Source record identifier from import |
part_number | VWG part code |
part_price | Part price (stored as text) |
local_currency | Currency code for the part price |
Behaviour
- Table is truncated and reloaded during import.
- Values are parsed during enquiry-time enrichment.
vwg_fluid_details
Purpose
Stores fluid metadata.
Structure
| Column | Description |
|---|---|
fluid_code | Fluid identifier |
description | Fluid description |
unit | Optional |
comments | Optional |
Behaviour
- Upserted using
fluid_codeas the key.
vwg_blacklisted_vin
Purpose
Stores VINs that must not trigger VWG API calls.
Behaviour
- Table is truncated and reloaded during import.
VWG API Integration
VWG Credential Resolution
VWG API credentials are resolved dynamically from Google Secret Manager using the vehicle manufacturer and vehicle type.
Secret Name
VWG_CLIENT_CREDENTIALS
Resolution Flow
- Credentials are fetched from Google Secret Manager.
- The secret value is base64-decoded and parsed as JSON.
- A matching configuration is selected using:
- Manufacturer
- Vehicle type
Returned Values
clientIdsecretwholesalerKeypartnerKey
Failure Behaviour
If credentials cannot be resolved:
- VWG processing is skipped
- Empty VWG result is returned
OAuth Token Request
Method
POST
Endpoint
/oauth2/token
Headers
Content-Type: application/x-www-form-urlencoded
Authorization: Basic base64(clientId:clientSecret)
Request Body
grant_type=client_credentials
Token Caching Behaviour
Tokens are cached until expiry.
Refresh Strategy
Tokens are refreshed early using a time buffer of 20% of the token's expires_in value.
This provides an approximate 20% safety buffer before token expiration.
Packages-by-VIN API
Method
POST
Endpoint
/{wholesalerKey}/{partnerKey}/getPackagesByVIN
Headers
Authorization: Bearer <access_token>
messageID: <uuid>
language: en-GB
aposVersion: LO
Brand: <manufacturerCode>
Content-Type: application/json
Accept: application/json
Request Body
{
"vin": "<vin>"
}
Runtime Processing
Enquiry-time steps from vehicle context and validation through the VWG API, response processing, and enrichment before data reaches the UI.
VIN Source of Truth
The VIN must always come from the VRM lookup response.
Behaviour
| Scenario | Behaviour |
|---|---|
| VIN available from VRM lookup | Continue VWG processing |
| VIN missing from VRM lookup | Skip VWG call and return empty result |
Vehicle Data Resolution Priority
Vehicle metadata is resolved using the following priority order:
- VRM Lookup
- Stock Details fallback
Fields
- Manufacturer
- Vehicle Type
When VWG Descriptions Are Fetched
Walk-In Enquiries
Descriptions are fetched inline during enquiry creation.
Flows
- Walk-in enquiry creation
- API walk-in enquiry creation
Both invoke:
saveFnpProviderJobDescriptions
Other Enquiry Types
Descriptions are fetched asynchronously after enquiry creation using the callable trigger flow.
VWG Pre-Flight Validation
If any validation fails, VWG processing returns an empty result.
| Validation | Requirement | Failure Result |
|---|---|---|
| Provider Gate | FNP provider must be OEM | Skip VWG |
| Lease Integration Gate | Lease config must allow VWG integration | Empty result |
| Supplier Franchise Gate | Supplier FNP enabled + manufacturer supported + franchise exists | Empty result |
| VRM VIN Gate | VIN must exist | Empty result |
| Manufacturer Gate | Manufacturer must belong to VWG group | Empty result |
| Vehicle Type Gate | Vehicle type required for credential resolution | Empty result |
VIN Blacklist Validation
Before calling VWG APIs:
-
VIN is checked against:
vwg_blacklisted_vin -
If VIN exists:
- VWG API call is skipped
- Empty result is returned
VWG Response Processing
Processing Pipeline
- Fetch packages-by-VIN response
- Validate response structure
- Extract part/fluid codes
- Resolve lookup maps from Cloud SQL
- Build enriched response payload
Response Validation
Expected package path:
carPackageSet.packageSet.package
Invalid Response Behaviour
If the package structure is missing or null:
[]
is returned.
Lookup Key Extraction
Unique lookup codes are extracted from all package items.
Part Codes
item.type === PART_CODE
Fluid Codes
item.type === FLUID_CODE
Codes are de-duplicated before querying Cloud SQL.
Cloud SQL Lookup Processing
Lookups are executed in batches.
Batch Size
Lookups are batched using PostgreSQL IN clauses. The exact batch size is an implementation detail (typically a few hundred codes per query) to stay within query length limits.
Part Price Lookup
Query
SELECT
part_number,
part_price
FROM vwg_parts_price
WHERE part_number IN (<batch>)
Map Construction
partPriceByCode[part_number] = parsedNumberOrNull(part_price)
Parsing Rules
| Value | Result |
|---|---|
| Valid numeric | Parsed number |
| Invalid/non-numeric | null |
Fluid Description Lookup
Query
SELECT
fluid_code,
description
FROM vwg_fluid_details
WHERE fluid_code IN (<batch>)
Map Construction
fluidDescriptionByCode[fluid_code] = description
Lookup Failure Behaviour
If Cloud SQL lookup processing fails:
- Enrichment continues
- Empty lookup maps are used
- Packages are still returned
Final Output Construction
Each VWG package becomes a single description entry.
Package Mapping
| Field | Mapping |
|---|---|
id | brandPackageId |
description | brandPackageId - <description> |
labourTime | Derived labour sum |
parts | Enriched line items |
Part Line Mapping
| Field | Value |
|---|---|
number | item.code |
name | item.desc |
quantity | Parsed amount |
price | Part price lookup result |
Fluid Line Mapping
| Field | Value |
|---|---|
number | item.code |
quantity | Parsed amount |
price | null |
name | Description lookup or fallback code |
UI Behaviour
Fluid line pricing is intentionally editable in the UI.
Behaviour
- Fluid unit price is not supplied by VWG
- UI allows manual user input
- Fluid descriptions are enriched from Cloud SQL when available
Conditions Summary
| Condition | Behaviour |
|---|---|
| VIN blacklisted | No VWG API call |
| Credentials missing | Empty result |
| Brand code missing | Empty result |
| Token fetch failure | Empty result |
| Invalid VWG response | Empty result |
| Non-numeric part price | Part price becomes null |
| Missing fluid description | Fluid code used as fallback |
Example Runtime Scenarios
Blacklisted VIN
Condition
VIN exists in:
vwg_blacklisted_vin
Result
- No VWG API call
- Empty response returned
Invalid Part Price
Condition
part_price contains non-numeric value.
Result
price = null
Missing Fluid Description
Condition
Fluid code not found in vwg_fluid_details
Result
Fluid code is displayed as the line name.
Logging
All VWG API calls are logged through:
repair-serve-callable
using:
logVwgApiCall
Firestore Log Structure
logs/vwgApi/{context}/{year}/{month}/{day}/log/{documentId}
Sensitive Data Masking
The following fields are masked before logging:
authorizationaccess_token